﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Xml;

/// <summary>
/// Summary description for clsDB
/// </summary>
public class clsDB
{
    public clsDB()
    {
        //
        // TODO: Add constructor logic here
        //

    }
    public static string foo()
    {
        string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["gamesConnectionString"].ConnectionString;
        return connStr;
    }
    public static DataTable RecentGames()
    {
        DataTable retDT = new DataTable();
        string conString = System.Configuration.ConfigurationManager.ConnectionStrings["gamesConnectionString"].ConnectionString;
        string strQuery = "select top 10 * from game";

        //SqlConnection myCon = new SqlConnection(conString);
        //SqlCommand myCmd = new SqlCommand(strQuery, myCon);
        //SqlDataAdapter myDA = new SqlDataAdapter(myCmd);
        //myCon.Open();
        //myDA.Fill(retDT);
        //myCon.Close();
        //return retDT;


        using (SqlConnection conn = new SqlConnection(conString))
        using (SqlCommand command = new SqlCommand(strQuery, conn))
        using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
            dataAdapter.Fill(retDT);
        return retDT;
    }
    public static string GetRecentGames()
    {
        DataTable retDT = new DataTable();
        string retString = "";
        string conString = System.Configuration.ConfigurationManager.ConnectionStrings["gamesConnectionString"].ConnectionString;
        string strQuery = "select top 30 * from game where active=1";

        // get DB data
        using (SqlConnection conn = new SqlConnection(conString))
        using (SqlCommand command = new SqlCommand(strQuery, conn))
        using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
            dataAdapter.Fill(retDT);

        // massage into HTML
        foreach (DataRow myDR in retDT.Rows)
        {
            string sTitle = myDR["title"].ToString();
            string sLTitle = myDR["latin_title"].ToString();
            retString += "<a href='/Game/" + sLTitle + "'><img alt='" + sTitle + "' class='thumb1' longdesc='" + sTitle + "' src='" + sLTitle + "' title='" + sTitle + "'></a>";
//            retString += "<a href='/Game/" + sLTitle + "'>   Game - " + sTitle + "</a><br />";
        }

        return retString;
    }
    public static DataTable GameInfo(string sLTitle)
    {
        DataTable retTable = ExecQuery("Select * from game where latin_title = '" + sLTitle + "'");


        return retTable;
    }
    public static string PlayGame(string sLTitle)
    {
        string retVal = "";
        string strGame = sLTitle;
        // begin stored proceedure
        string conString = System.Configuration.ConfigurationManager.ConnectionStrings["gamesConnectionString"].ConnectionString;
        DataTable retTable = new DataTable();
        try
        {
            using (SqlConnection conn = new SqlConnection(conString))
            using (SqlCommand command = new SqlCommand("PlayGame", conn))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@latin_title", sLTitle);
                conn.Open();
                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                    dataAdapter.Fill(retTable);
            }
            // end stored proceedure

            DataRow myDR = retTable.Rows[0];
            string sTitle = myDR["title"].ToString();
            //string sLTitle = myDR["latin_title"].ToString();
            string sSwf = myDR["swf"].ToString();
            string sDesc = myDR["description"].ToString();
            string sCont = myDR["controls"].ToString();
            int iHeight = Convert.ToInt32(myDR["height"].ToString());
            int iWidth = Convert.ToInt32(myDR["width"].ToString());


            retVal += "<h1>" + sTitle + "</h1>";
            retVal += "<object classid='clsid:D27CDB6E-AE6D-11cf-96B8-444553540000' codebase='http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=7,0,19,0' width='" + iWidth + "' height='" + iHeight + "'>";
            retVal += "<param name='movie' value='" + sSwf + "'>";
            retVal += "<param name='quality' value='high'>";
            retVal += "<embed src='" + sSwf + "' quality='high' pluginspage='http://www.macromedia.com/go/getflashplayer' type='application/x-shockwave-flash' width='640' height='520'></embed>";
            retVal += "</object>";
            if (sDesc.Length > 0)
            {
                retVal += "<h2>Description</h2>";
                retVal += System.Web.HttpUtility.HtmlDecode(sDesc);
            }
            if (sCont.Length > 0)
            {
                retVal += "<h2>Controls</h2>";
                retVal += System.Web.HttpUtility.HtmlDecode(sCont);
            }

            ExecNonQuery("update game set plays_total=plays_total + 1 where latin_title='" + sLTitle + "'");

            return retVal;
        }
        catch {
            
            return "";
        }
    }
    public static string PlayGame2(string sLTitle)
    {
        string retVal = "";
        DataTable retTable = ExecQuery("Select * from game where latin_title = '" + sLTitle + "'");
        string strGame = sLTitle;
        DataTable myDT = clsDB.GameInfo(strGame);
        DataRow myDR = myDT.Rows[0];
        string sTitle = myDR["title"].ToString();
        //string sLTitle = myDR["latin_title"].ToString();
        string sSwf = myDR["swf"].ToString();
        string sDesc = myDR["description"].ToString();
        string sCont = myDR["controls"].ToString();
        int iHeight = Convert.ToInt32(myDR["height"].ToString());
        int iWidth = Convert.ToInt32(myDR["width"].ToString());


        retVal += "<h1>" + sTitle + "</h1>";
        retVal += "<object classid='clsid:D27CDB6E-AE6D-11cf-96B8-444553540000' codebase='http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=7,0,19,0' width='" + iWidth + "' height='" + iHeight + "'>";
        retVal += "<param name='movie' value='" + sSwf + "'>";
        retVal += "<param name='quality' value='high'>";
        retVal += "<embed src='" + sSwf + "' quality='high' pluginspage='http://www.macromedia.com/go/getflashplayer' type='application/x-shockwave-flash' width='640' height='520'></embed>";
        retVal += "</object>";
        if (sDesc.Length > 0)
        {
            retVal += "<h2>Description</h2>";
            retVal += sDesc;
        }
        if (sCont.Length > 0)
        {
            retVal += "<h2>Controls</h2>";
            retVal += sCont;
        }

        ExecNonQuery("update game set plays_total=plays_total + 1 where latin_title='" + sLTitle + "'");

        return retVal;
    }
    public static DataTable ExecQuery(string sQuery)
    {
        DataTable retDT = new DataTable();
        string conString = System.Configuration.ConfigurationManager.ConnectionStrings["gamesConnectionString"].ConnectionString;
        //string strQuery = "select top 10 * from game";

        using (SqlConnection conn = new SqlConnection(conString))
        using (SqlCommand command = new SqlCommand(sQuery, conn))
        using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
            dataAdapter.Fill(retDT);
        return retDT;
    }
    public static void ExecNonQuery(string sQuery)
    {
        string conString = System.Configuration.ConfigurationManager.ConnectionStrings["gamesConnectionString"].ConnectionString;
        //string strQuery = "select top 10 * from game";

        using (SqlConnection conn = new SqlConnection(conString))
        using (SqlCommand command = new SqlCommand(sQuery, conn))
        {
            conn.Open();
            command.ExecuteNonQuery();
        }
    }
    public static string GetTopCategories()
    {
        string retVal = clsDB.GetTopCategories(25);
        return retVal;
    }
    public static string GetTopCategories(int iNum = 25)
    {
        string retVal = "<ul class='nav1'>";

        string strQuery = "select top "+ iNum +" c.category from category c inner join CatGame cg on c.id=cg.category_id group by c.category order by count(*) desc";
        DataTable myDT = ExecQuery(strQuery);
        foreach (DataRow myDR in myDT.Rows)
        {
            string catName = myDR["Category"].ToString();
            //catName = System.Web.HttpUtility.HtmlEncode(catName);
            //catName = catName.Replace("&", "%26");
            retVal += "<li class='nav1'><a href='/Category/" + catName.Replace("&","|") + "'>" + catName + "</a></li>";
        }

        retVal += "</ul>";
        return retVal;
    }
    public static string GetCategories()
    {
        string retVal = "<ul class='nav1'>";

        string strQuery = "select c.category from category c inner join CatGame cg on c.id=cg.category_id group by c.category order by count(*) desc";
        DataTable myDT = ExecQuery(strQuery);
        foreach (DataRow myDR in myDT.Rows)
        {
            string catName = myDR["Category"].ToString();
            catName = System.Web.HttpUtility.UrlEncode(catName);
            retVal += "<li class='nav1'><a href='/Category/" + catName + "'>" + catName + "</a></li></br>";
        }

        retVal += "</ul>";
        return retVal;
    }
    public static string GetLargeImages()
    {
        string retVal = "";
        string strQuery = "select top 10 * from game where large_img <>'' order by featured desc, NEWID() ";
        DataTable myDT = ExecQuery(strQuery);
        int i = 0;
        foreach (DataRow myDR in myDT.Rows)
        {
            string largeImg = myDR["large_img"].ToString();
            string sTitle = myDR["title"].ToString();
            string sLTitle = myDR["latin_title"].ToString();
            string sDesc = myDR["description"].ToString();
            if (i == 0)
            {
                retVal += "<div class=active><a href='/Game/" + sLTitle + "'><img alt='" + sTitle + "' longdesc='" + sTitle + "' src='" + largeImg + "' title='" + sTitle + "' class='largeImg'></a></div>";
            }
            else
            {
                retVal += "<div><a href='/Game/" + sLTitle + "'><img alt='" + sTitle + "' longdesc='" + sTitle + "' src='" + largeImg + "' title='" + sTitle + "' class='largeImg'></a></div>";
            }
            i++;
        }

        return retVal;
    }
    public static string GetRandomGame()
    {
        string retVal = "";
        string strQuery = "SELECT TOP 1 latin_title FROM game ORDER BY NEWID()";
        DataTable myDT = ExecQuery(strQuery);
        retVal = myDT.Rows[0]["latin_title"].ToString();
        return retVal;
    }
    public static void UpdateCategories()
    {
        // clear out Unity games
        ExecNonQuery("delete game where keywords like '%unity%'");
        // clear out old data
        string strSql = "truncate table category;";
        strSql += "truncate table catgame;";
        // populate new categories
        strSql += "insert category select distinct category from game;";
        // link categories and games
        strSql += "insert CatGame select c.id, g.id from category c inner join game g on c.category=g.category;";
        ExecNonQuery(strSql);

        // update with keywords
        // query keywords column
        strSql = "select distinct keywords from game";
        DataTable myDT = ExecQuery(strSql);
        // split keywords column into a dictionary object
        Dictionary<string, string> dictKey = new Dictionary<string, string>();
        foreach(DataRow myDR in myDT.Rows)
        {
            string[] fullKey = myDR["keywords"].ToString().Split(',');
            foreach(string key in fullKey)
            {
                if (!dictKey.ContainsKey(key))
                    dictKey.Add(key, key);
            }
        }
        List<string> myKeys= new List<string>(dictKey.Keys);
        foreach(string sKey in myKeys)
        {
            strSql = "if not exists (select * from category where category='" + sKey.Trim() + "') Insert category (category) values('" + sKey.Trim() + "');";
            ExecNonQuery(strSql);
        }
        strSql += "insert CatGame select c.id, g.id from category c inner join game g on  g.keywords like '%'+c.category+'%';";
        ExecNonQuery(strSql);


    }
    public static void SetupDB(string strSql)
    {

        ExecQuery(strSql);
    }
    public static void CreateSitemap()
    {
        String strPathAndQuery = HttpContext.Current.Request.Url.PathAndQuery;
        String strUrl = HttpContext.Current.Request.Url.AbsoluteUri.Replace(strPathAndQuery, "/");
        // create new sitemap
        // populate default.aspx info
        string smXML = "<?xml version='1.0' encoding='UTF-8'?> <urlset xmlns='http://www.sitemaps.org/schemas/sitemap/0.9' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:schemaLocation='http://www.sitemaps.org/schemas/sitemap/0.9 http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd'><url><loc>" + strUrl + "</loc><changefreq>monthly</changefreq><priority>1.00</priority></url></urlset>";
        XmlDocument myDoc = new XmlDocument();
        myDoc.LoadXml(smXML);
        XmlNamespaceManager nsMgr = new XmlNamespaceManager(myDoc.NameTable);
        string strNamespace= myDoc.DocumentElement.NamespaceURI;
        nsMgr.AddNamespace("ns", strNamespace);

        XmlNode root = myDoc.SelectSingleNode("/ns:urlset", nsMgr);

        // get information from DB
        DataTable myDT = ExecQuery("select 'category/'+category as 'loc', '0.8' as priority, 'monthly' as changefreq from category union select 'game/'+latin_title as 'loc', '0.5' as priority, 'monthly' as changefreq  from game");

        // cycle through categories/games and add info
        foreach(DataRow myDR in myDT.Rows)
        {
            using (XmlWriter writer = root.CreateNavigator().AppendChild())
            {
                writer.WriteStartElement("url", strNamespace);
                writer.WriteElementString("loc", strNamespace, strUrl + myDR["loc"].ToString());
                writer.WriteElementString("changefreq", strNamespace, myDR["changefreq"].ToString());
                writer.WriteElementString("priority", strNamespace, myDR["priority"].ToString());
                writer.WriteEndElement();
            }
        }

        // save file
        string targetDir = HttpContext.Current.Server.MapPath("~");
        string sitemapFile = targetDir + "/sitemap.xml";
        myDoc.Save(sitemapFile);
    }
}